if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Map_MGH_DailyPatientCareCharge]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Map_MGH_DailyPatientCareCharge]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/***************************************************************************
* Name:			Map_MGH_DailyPatientCareCharge '20080501'
* Purpose:		Using table Stg_MGH_DailyPatientCareCharge, map, and validate
*				feed data.
*
* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
* n/a
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
* @_exitStatus		An exit status of either true or false
***************************************************************************/
CREATE PROCEDURE [dbo].[Map_MGH_DailyPatientCareCharge] 
( 
	@feedDateString varchar(8)
)
AS 
BEGIN

	SET NOCOUNT ON

	declare @FeedDate datetime
	set @FeedDate = substring(@feedDateString,5,2)+'/'+substring(@feedDateString,7,2)+'/'+substring(@feedDateString,1,4)
/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	M A P  A N D  V A L I D A T E  D A T A
	Convert dates from strings to real datetime stamp.
	Map the fund number to a fund ID.  Map Patent ID.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	-- Convert the Post date
	update stg_MGH_DailyPatientCareCharge 
	set ValidatedPostDate = substring(PostDate,5,2)+ '/' 
							+ substring(PostDate,7,2) + '/' 
							+ substring(PostDate,1,4)
	where len(isNull(PostDate,''))=8 

	-- Convert the Service date
	update stg_MGH_DailyPatientCareCharge 
	set ValidatedServiceDate = substring(ServiceDate,5,2)+ '/' 
								+ substring(ServiceDate,7,2) + '/' 
								+ substring(ServiceDate,1,4)
	where len(isNull(ServiceDate,''))=8 

	-- Place sign in front of negative numbers instead of on the end.
	update stg_MGH_DailyPatientCareCharge 
	set ExtendedAmount = '-'+left(ltrim(rtrim(ExtendedAmount)),len(rtrim(ltrim(ExtendedAmount)))-1)
	where charindex('-',ltrim(rtrim(ExtendedAmount)),Len(ltrim(rtrim(ExtendedAmount)))-2)<>0

	-- Map the fund number to a fund on a research project.
	Update s set fundid = aaf.agrfundid
	from stg_MGH_DailyPatientCareCharge s 
	inner join agrFund_agragreement_map aaf on aaf.fundnumber = s.fundnumber 

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	M A P  P A T I E N T  N A M E  T O  L A S T ,  F I R S T  A N D  M I D D L E
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	Update s set PatientLastName = PatientName
		   ,PatientFirstName =''
		   ,PatientMiddleName = ''
	From stg_MGH_DailyPatientCareCharge s
	where charindex(',',PatientName,1)=0

	Update s set PatientLastName = left(rtrim(ltrim(PatientName)),charindex(',',PatientName,1)-1)
		   ,PatientFirstName = right(rtrim(ltrim(PatientName)),len(rtrim(ltrim(PatientName)))-charindex(',',rtrim(ltrim(PatientName)),1))
	From stg_MGH_DailyPatientCareCharge s
	where charindex(',',PatientName,1)<>0

	Update s set PatientMiddleName = ''
	From stg_MGH_DailyPatientCareCharge s
	where charindex(',',rtrim(ltrim(PatientFirstName))) = 0 and charindex(' ',rtrim(ltrim(PatientFirstName))) = 0

	Update s set PatientMiddleName = right(rtrim(ltrim(PatientFirstName))
				,len(rtrim(ltrim(PatientFirstName))) - charindex(',',rtrim(ltrim(PatientFirstName))))
				,PatientFirstName = left(rtrim(ltrim(PatientFirstName)),charindex(',',rtrim(ltrim(PatientFirstName)))-1)
	From stg_MGH_DailyPatientCareCharge s
	where charindex(',',rtrim(ltrim(PatientFirstName))) <> 0 

	Update s set PatientMiddleName = right(rtrim(ltrim(PatientFirstName))
				,len(rtrim(ltrim(PatientFirstName))) - charindex(' ',rtrim(ltrim(PatientFirstName))))
				,PatientFirstName = left(rtrim(ltrim(PatientFirstName)),charindex(' ',rtrim(ltrim(PatientFirstName)))-1)
	From stg_MGH_DailyPatientCareCharge s
	where charindex(',',rtrim(ltrim(PatientFirstName))) = 0 and charindex(' ',rtrim(ltrim(PatientFirstName))) <> 0

/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	S C R A P E  P A T I E N T S  O N  M R N
	Sometimes the MRN is invalid and is not an integer value.
	Therefore we will only scrape those with a numeric MRN.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	if object_id('tempdb..#PatientMRN') is not null drop table #PatientMRN
	if object_id('tempdb..#PatientMRN1') is not null drop table #PatientMRN1
	if object_id('tempdb..#DupPatient') is not null drop table #DupPatient

	select s.patientMRN,s.PatientFirstName,s.PatientLastName,s.PatientMiddleName,count(*) as TheCount into #PatientMRN 
	From stg_MGH_DailyPatientCareCharge s
	group by s.patientMRN,s.PatientFirstName,s.PatientLastName,s.PatientMiddleName

	select row_Number() over (Order by PatientMRN) as Id,* into #PatientMRN1 from  #PatientMRN 

	select patientMRN,count(*) as theCount into #DupPatient from #PatientMRN1 group by patientMRN 

	Insert into Patient 
		   ([PatientMRN]
		   ,[FirstName]
		   ,[LastName]
		   ,[MiddleName])
	 Select distinct
		   p.patientMRN
		   ,p.PatientFirstName
		   ,p.PatientLastName
		   ,p.PatientMiddleName
	from #DupPatient d inner join #PatientMRN1 p on p.patientMRN = d.patientMRN 
													and p.id = (select max(p1.id) from #PatientMRN1 p1 
																where p1.PatientMRN = p.PatientMRN)	
	Where	not exists(Select 1 from Patient p1 where p.PatientMRN = p1.patientMRN)

	/* Map to the patient charge rows to the corresponding patient table row.
	*/
	Update s set PatientId = p.Id
	from stg_MGH_DailyPatientCareCharge s
	inner join Patient p on s.PatientMRN = p.PatientMRN

/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	S C R A P E  C H A R G E  C O D E  D A T A
	Scrape charge code data if it does not already exist.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
INSERT INTO [dbo].[PatientCareChargeCode]
           ([Institution]
           ,[Department]
           ,[Location]
           ,[ChargeCode]
           ,[ChargeDescription]
           ,[CPTCode])
     select distinct
           'MGH'
           ,'' as 'Department'
           ,'' as 'Location'
           ,ChargeCode
           ,Description
           ,CPTCode
	from stg_MGH_DailyPatientCareCharge s
	where not exists(select 1 from [dbo].[PatientCareChargeCode] r 
					 where r.chargecode = s.chargecode 
							and r.Institution = 'MGH')		

	-- Map the Charge code to the patient care rate Id .  This is date oriented.
	-- We need to pick the date (Service or Post) date.
	Update s set PatientCareChargeCodeId = c.id
	from stg_MGH_DailyPatientCareCharge s 
	inner join PatientCareChargeCode c on c.Institution = 'MGH' and c.ChargeCode = s.ChargeCode
								 

	-- Map the Charge code to the patient care rate Id .  This is date oriented.
	-- We need to pick the date (Service or Post) date.
	Update s set PatientCareEffectivePriceId = pep.id
	from stg_MGH_DailyPatientCareCharge s 
	inner join PatientCareChargeCode pcc on pcc.Institution = 'MGH' and pcc.ChargeCode = s.ChargeCode
	inner join PatientCareEffectivePrice pep on pep.PatientCareChargeCodeId = pcc.Id
										and pep.EffectiveDate = (select max(pep1.effectivedate) 
																 from PatientCareEffectivePrice pep1
																 where pep1.PatientCareChargeCodeId = pep.PatientCareChargeCodeId
																		and pep1.effectivedate <= s.ValidatedServiceDate)

	-- Assign the feed date to the new rows in staging.
	update stg_MGH_DailyPatientCareCharge set feeddate = @FeedDate where feeddate is null

	-- Flag all with this feed date as initially invalid.
	update stg_MGH_DailyPatientCareCharge set IsValid = 0 where feedDate = @feedDate

	-- Flag all the valid rows based on the presence of all the appropriate mapped values.
	update stg_MGH_DailyPatientCareCharge set IsValid = 1 
	where IsNull(fundid,0) <> 0 and IsNull(PatientId,0)<>0 and IsNull(PatientCareChargeCodeId,0)<>0  

	-- Validate that extended amount is numeric and move it to a money column.
	update stg_MGH_DailyPatientCareCharge set ValidatedExtendedAmount = convert(money,ExtendedAmount)
	where IsNumeric(ExtendedAmount)=1

	-- Validate that quantity is numeric and move it to a decimal column.
	update stg_MGH_DailyPatientCareCharge set ValidatedQuantity = convert(decimal(10,5),Quantity)
	where IsNumeric(Quantity)=1

	-- Invalid if not numeric in these columns.
	update stg_MGH_DailyPatientCareCharge set IsValid = 0 where IsNumeric(Quantity)=0 or IsNumeric(ExtendedAmount)=0
 
	-- Set the discount and net amount column.
	update s 
	set Discount =  
			convert(money,s.ValidatedExtendedAmount) 
						- round(pep.ResearchRate * convert(money,s.ValidatedExtendedAmount),2)--Discount amount
		,NetAmount =  
			round(pep.ResearchRate  * convert(money,s.ValidatedExtendedAmount),2) --Research amount
	from stg_MGH_DailyPatientCareCharge s
	inner join PatientCareEffectivePrice pep on pep.id = s.PatientCareEffectivePriceId		
	where s.IsValid = 1

	SET NOCOUNT OFF

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO